import numpy as np
import pandas as pd

# 读取数据
data = pd.read_excel('./assets/data.xlsx')

# 获取有效数据
years = data.columns[1:].astype(int)
visitor = pd.to_numeric(data.iloc[0, 1:], errors='coerce').values
satisfaction = pd.to_numeric(data.iloc[1, 1:], errors='coerce').values
expanditure = pd.to_numeric(data.iloc[2, 1:], errors='coerce').values
tax = pd.to_numeric(data.iloc[3, 1:], errors='coerce').values
investment = pd.to_numeric(data.iloc[4, 1:], errors='coerce').values
glacier = pd.to_numeric(data.iloc[5, 1:], errors='coerce').values

# 插值
interp_years = np.arange(years.min(), years.max() + 1, 1)
interp_visitor = np.interp(interp_years, years[~np.isnan(visitor)], visitor[~np.isnan(visitor)])
interp_satisfaction = np.interp(interp_years, years[~np.isnan(satisfaction)], satisfaction[~np.isnan(satisfaction)])
interp_expanditure = np.interp(interp_years, years[~np.isnan(expanditure)], expanditure[~np.isnan(expanditure)])
interp_tax = np.interp(interp_years, years[~np.isnan(tax)], tax[~np.isnan(tax)])
interp_investment = np.interp(interp_years, years[~np.isnan(investment)], investment[~np.isnan(investment)])
interp_glacier = np.interp(interp_years, years[~np.isnan(glacier)], glacier[~np.isnan(glacier)])

# 数据归一化
visitor_mean = np.mean(interp_visitor)
visitor_std = np.std(interp_visitor)
normalized_visitors = (interp_visitor - visitor_mean) / visitor_std

satisfaction_mean = np.mean(interp_satisfaction)
satisfaction_std = np.std(interp_satisfaction)
normalized_satisfaction = (interp_satisfaction - satisfaction_mean) / satisfaction_std

expanditure_mean = np.mean(interp_expanditure)
expanditure_std = np.std(interp_expanditure)
normalized_expanditure = (interp_expanditure - expanditure_mean) / expanditure_std

tax_mean = np.mean(interp_tax)
tax_std = np.std(interp_tax)
normalized_tax = (interp_tax - tax_mean) / tax_std

investment_mean = np.mean(interp_investment)
investment_std = np.std(interp_investment)
normalized_investment = (interp_investment - investment_mean) / investment_std

glacier_mean = np.mean(interp_glacier)
glacier_std = np.std(interp_glacier)
normalized_glacier = (interp_glacier - glacier_mean) / glacier_std

print("visitor_mean = ", visitor_mean)
print("visitor_std = ", visitor_std)
print("satisfaction_mean = ", satisfaction_mean)
print("satisfaction_std = ", satisfaction_std)
print("expanditure_mean = ", expanditure_mean)
print("expanditure_std = ", expanditure_std)
print("tax_mean = ", tax_mean)
print("tax_std = ", tax_std)
print("investment_mean = ", investment_mean)
print("investment_std = ", investment_std)
print("glacier_mean = ", glacier_mean)
print("glacier_std = ", glacier_std)

# 保存到 Excel 文件
data = {
    "Years": interp_years,
    "Visitor": normalized_visitors,
    "Satisfaction": normalized_satisfaction,
    "Expanditure": normalized_expanditure,
    "Tax": normalized_tax,
    "Investment": normalized_investment,
    "Glacier": normalized_glacier
}

df = pd.DataFrame(data)
df.to_excel("./assets/normalized_data.xlsx", index=False)